<?php
namespace app\admin\controller;
use cmf\controller\AdminBaseController;
use think\Db;
use app\admin\model\NgeAgentModel;
use app\admin\model\NgeAdvertisingaccountModel;
use think\Loader;
use think\Request;
use think\paginator\driver\Bootstrap;

class ManageController extends AdminBaseController
{

    public function _initialize()
    {
        parent::_initialize();
    }
    
	// //导入域名
	// public function domainExcelAdd()
	// {
	// 	if($this->request->isPost()){

	// 		$excel_name = $this->request->param('excel_name');
 //        	$excel_url = $this->request->param('excel_url');

 //        	if(!empty($excel_name) && !empty($excel_url)){

 //        		$excelData = $this->excelRead($excel_url);

 //                if ($excelData[0][4] != '主域名') {
 //                    $this->error('确认表格是否正确');
 //                }

 //                array_shift($excel_array);  //删除第一个数组(标题);

 //        		$domain = Db::name('nge_domain')->field('domain')->select()->toArray();
			
 //                $domainAll = [];
 //        		foreach ($domain as $ddk => $ddv) {
 //        			$domainAll[$ddk] = $ddv['domain'];
 //        		}

 //        		$data = [];
 //        		//整合数据
 //        		foreach ($excelData as $key => $value) {
 //        			//匹配是否有重复信息,重复信息跳过
 //        			if(in_array($value[4], $domainAll)){
 //        				continue;
 //        			}
        			
 //        			$data[$key]['buy_platform'] 	= isset($value[0]) ? $value[0] : '';
 //        			$data[$key]['platform_account'] = isset($value[1]) ? $value[1] : '';
 //        			$data[$key]['group'] 			= isset($value[2]) ? $value[2] : '';
 //        			$data[$key]['domain'] 			= isset($value[4]) ? $value[4] : '';
 //        			$data[$key]['stationmaster'] 	= isset($value[3]) ? $value[3] : '';
 //        			$data[$key]['create_time'] 		= time();

 //        			if ($value[5] != null) {
 //        				$data[$key]['sub_domain'] = $value[5];
 //        			}else{
 //        				$data[$key]['sub_domain'] = '';
 //        			}
 //        			if ($value[6] != null) {
 //        				$data[$key]['sub_domain'] = $data[$key]['sub_domain'].','.$value[6];
 //        			}
 //        			if ($value[7] != null) {
 //        				$data[$key]['sub_domain'] = $data[$key]['sub_domain'].','.$value[7];
 //        			}
 //        			if ($value[8] != null) {
 //        				$data[$key]['sub_domain'] = $data[$key]['sub_domain'].','.$value[8];
 //        			}

        			
 //        		}

 //        		if ($data) {
 //        			Db::name('nge_domain')->insertAll($data);
 //        			$this->success('导入成功' , url("manage/domainList"));
 //        		}else{
 //        			$this->error('没有数据导入');
 //        		}
 //        	}else{
 //        		$this->error('请上传Excel文件');
 //        	}
	// 	}
	// }



        //导入域名
    public function domainExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excelData = $this->excelRead($excel_url);

                $domainIdNum = Db::name('nge_domain')->count('id');

                if ($excelData[0][5] != '主域名') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excelData);  //删除第一个数组(标题);           
                //整合数据
                foreach ($excelData as $key => $value) {
                    
                    $data['buy_platform']     = isset($value[1]) ? $value[1] : '';
                    $data['platform_account'] = isset($value[2]) ? $value[2] : '';
                    $data['group']            = isset($value[3]) ? $value[3] : '';
                    $data['stationmaster']    = isset($value[4]) ? $value[4] : '';
                    $data['domain']           = isset($value[5]) ? strtolower($value[5]) : '';
                    $data['create_time']      = time();

                    if ($value[6] != null) {
                        $data['sub_domain'] = $value[6];
                    }else{
                        $data['sub_domain'] = '';
                    }
                    if ($value[7] != null) {
                        $data['sub_domain'] = $data['sub_domain'].','.$value[7];
                    }
                    if ($value[8] != null) {
                        $data['sub_domain'] = $data['sub_domain'].','.$value[8];
                    }
                    if ($value[9] != null) {
                        $data['sub_domain'] = $data['sub_domain'].','.$value[9];
                    }

                    if ($value[0] > $domainIdNum) {
                        $re = Db::name('nge_domain')->insert($data);
                    }else{
                        $re = Db::name('nge_domain')->where('id',$value[0])->update($data); 
                    }
                    
                }

                if ($re) {
                    $this->success('导入成功' , url("manage/domainList"));
                }else{
                    $this->error('导入失败');
                }
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

	//域名列表
	public function domainList()
	{
		$content = hook_one('admin_manage_domainlist_view');
		if (!empty($content)) {
			return $content;
		}

        $domainname = trim($this->request->param('domainname'));
        $where = [];
        if($domainname){
            $where['domain|sub_domain|stationmaster'] = ['like','%'.$domainname.'%'];
        }

        $domains = Db::name('nge_domain')
                    ->where($where)
                    ->order('id DESC')
                    ->paginate(20, false, [
                            'query' => request()->param(),
                        ]);

        $page = $domains->render();
        $this->assign("page", $page);
		$this->assign('domains',$domains);

		return $this->fetch();
	}

    public function editdomain(){
        $id = $this->request->param('id', 0, 'intval');
        $domainname = DB::name('nge_domain')->where(["id" => $id])->find();
        $this->assign($domainname);
        return $this->fetch();
    }

    // 单个编辑域名提交
    public function editPostDomain(){
        if ($this->request->isPost()) {
            if (!empty($_POST['id'])) {
                $result = DB::name('nge_domain')->update($_POST);
                if ($result !== false) {
                        $this->success("修改成功！", url("manage/domainList"));
                    } else {
                        $this->error("修改失败！");
                    }
            } else {
                $this->error("ID不能为空！");
            }
        }
    }

	//ajax更改是否更换站长字段
	public function ajaxChangeMaster()
	{
		if ($this->request->isAjax()) {
			$id = $this->request->post('id');
			$changeMaster = $this->request->post('change_master');
			if ($changeMaster && $id) {
				if(Db::name()->where(['id' => $id])->update(['change_master' => $changeMaster])){
					return json_encode('修改成功');
				}else{
					return json_encode('修改失败');
				}
			}else{
				return false;
			}
		}
	}

	//代理导入
	public function agentExcelAdd()
	{
		if($this->request->isPost()){

			$excel_name = $this->request->param('excel_name');
        	$excel_url = $this->request->param('excel_url');

        	if(!empty($excel_name) && !empty($excel_url)){

        		$excelData = $this->excelRead($excel_url);

                if ($excelData[1][1] != '代理') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excelData);  //删除第一个数组(标题);

        		array_shift($excelData);	//这个表有2行头,再去掉第一行
        		$agent = Db::name('nge_agent')->field('name')->select()->toArray();

        		$data = [];
			
                $agentAll = [];

        		foreach ($agent as $aak => $aav) {
        			$agentAll[$aak] = $aav['name'];
        		}
        		//整合数据
        		foreach ($excelData as $key => $value) {
        			//匹配是否有重复信息,重复信息跳过
        			if(in_array($value[1], $agentAll)){
        				continue;
        			}
        			if (strlen($value[1]) <= 0) {
        				continue;
        			}
        			$data[$key]['name'] 			= isset($value[1]) ? $value[1] : '';		
        		}

        		if ($data) {
        			Db::name('nge_agent')->insertAll($data);
        			$this->success('导入成功' , url("manage/agentList"));
        		}else{
        			$this->error('没有数据导入');
        		}
        	}else{
        		$this->error('请上传Excel文件');
        	}
		}
	}

	//代理列表
	public function agentList()
	{
		$content = hook_one('admin_manage_agentlist_view');
		if (!empty($content)) {
			return $content;
		}

		$name = trim($this->request->param('name'));
        $where = [];
        if($name){
            $where['name'] = ['like','%'.$name.'%'];
        }
        $agent = new NgeAgentModel;
        $agents = $agent->with('adaccounts')->where($where)->order('id DESC')->paginate(10, false, [
                            'query' => request()->param(),
                        ]);

        $page = $agents->render();
        $this->assign("page", $page);
		$this->assign('agents',$agents);

		return $this->fetch();
	}

    public function editagent(){
        $id = $this->request->param('id', 0, 'intval');

        $agentModel = new NgeAgentModel;
        $agent = $agentModel->with('adaccounts')->where(['id'=>$id])->find();
        $this->assign('agent',$agent);
        return $this->fetch();
    }

    // 单个编辑代理提交
    public function editPostAgent(){
        if ($this->request->isPost()) {
            if (!empty($_POST['id'])) {
                $flag = false;
                foreach ($_POST['advaccount'] as $key => $value) {
                    $tp['id'] = $value['id'];
                    $tp['account_num'] = $value['account_num'];
                    $tp['status'] = $value['status'];
                    $tp['down_time'] = strtotime($value['down_time']);

                    $result = DB::name('nge_advertisingaccount')->update($tp);
                    if($result){
                        $flag = true;
                    }
                }
                if($flag){
                    $this->success("修改成功！", url("manage/agentList"));
                }else{
                    $this->error("修改失败,您可能未修改任何内容");
                }
            } else {
                $this->error("ID不能为空！");
            }
        }
    }

    //代理删除
    public function delAgent()
    {
        $id = $this->request->param('id', 0, 'intval');
        if ($id) {
            $re = Db::name('nge_agent')->delete($id);
            if ($re) {
                $this->success('删除成功');
            }else{
                $this->error('删除失败');
            }
        }
    }

	//导入虚拟机
    public function vminfoExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excelData = $this->excelRead($excel_url);

                if ($excelData[0][0] != 'FB账号') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excelData);  //删除第一个数组(标题);

                $vminfo = Db::name('nge_vminfo')->field('vim_ip')->select();

                $vminfoAll = [];
                foreach ($vminfo as $vvk => $vvv) {
                    $vminfoAll[$vvk] = $vvv['vim_ip'];
                }

                $data = [];
                //数据中一条对应多条数据的,整理成数组
                foreach ($excelData as $ek => $ev) {
                    if($ev[1] == null && $ev[2] == null && $ev[3] == null && ($ev[4] !== null && $ev[7] != null)){
                        $flag +=1;
                        $a['accountnumber'] = $ev[4];
                        $a['bm'] = $ev[6];
                        $a['url'] = $ev[7];

                        $excelData[$ek-$flag][100][] = $a;

                        unset($excelData[$ek]);
                    }
                    else{
                        $flag = 0;
                        $a['accountnumber'] = $ev[4];
                        // $a['password'] = $ev[4];
                        $a['bm'] = $ev[6];
                        $a['url'] = $ev[7];

                        $excelData[$ek][100][] = $a;
                    }
                }

                //重置数组键序
                $excelData = array_merge($excelData);

                //整合数据
                foreach ($excelData as $key => $value) {
                    //匹配是否有重复信息,重复信息跳过
                    if(is_array($vminfoAll) && in_array($value[1], $vminfoAll)){
                        continue;
                    }
                    //x先添加关联表数据
                    foreach ($value[100] as $vk => $vv) {
                        //查询subordinate中是否有相同的accountnumber
                        $acre = Db::name('nge_subordinate')->where(['accountnumber' => $vv['accountnumber']])->find();
                        if (is_array($acre)) {
                            //如果查到了更新bm数据
                            if ($vv['bm'] != null) {
                                $bms = $acre['bm'].','.$vv['bm'];
                                $bms = implode(',',array_unique(explode(',',$bms)));
                            }else{
                                $bms = $acre['bm'];
                            }
        
                            Db::name('nge_subordinate')->where(['accountnumber' => $vv['accountnumber']])->update(['bm' => $bms]);
                            $suId = $acre['id'];

                            //查询粉丝页是否存在
                            $fans = Db::name('nge_fanpages')->where(['url'=>$vv['url']])->find();
                            if (is_array($fans)) {
                                // //如果查到了更新subordinate_id数据
                                // $subordinate_ids = $fans['subordinate_id'].','.$vv['subordinate_id'];
                                // $subordinate_ids = implode(',',array_unique(explode(',',$fans)));
                                // Db::name('nge_subordinate')->where(['url' => $vv['url']])->update(['subordinate_id' => $subordinate_ids]);
                            }else{
                                //插入粉丝页
                                Db::name('nge_fanpages')->insert([
                                    'url'               => $vv['url'],
                                    'status'            => 1,
                                    'subordinate_id'    => $suId,
                                    'in_bm'             => $vv['bm'],
                                    'create_time'       => time(),
                                    ]);
                            }
                        }else{
                            //添加subordinate所属表数据
                            Db::name('nge_subordinate')->insert([
                                'accountnumber' => $vv['accountnumber'],
                                // 'password'       => $vv['password'],
                                'bm'            => $vv['bm'],
                                'creat_time'    => time()
                                ]);
                            $suId = Db::name('nge_subordinate')->getLastInsID();

                            //查询粉丝页是否存在
                            $fans = Db::name('nge_fanpages')->where(['url'=>$vv['url']])->find();
                            if (is_array($fans)) {
                                // //如果查到了更新subordinate_id数据
                                // $subordinate_ids = $fans['subordinate_id'].','.$vv['subordinate_id'];
                                // $subordinate_ids = implode(',',array_unique(explode(',',$fans)));
                                // Db::name('nge_subordinate')->where(['url' => $vv['url']])->update(['subordinate_id' => $subordinate_ids]);
                            }else{
                                //插入粉丝页
                                Db::name('nge_fanpages')->insert([
                                    'url'               => $vv['url'],
                                    'status'            => 1,
                                    'subordinate_id'    => $suId,
                                    'in_bm'             => $vv['bm'],
                                    'create_time'       => time(),
                                    ]);
                            }
                        }
                        $suIds = isset($suIds) ? $suIds . ',' . $suId : $suId;
                    }
                    $suIds = implode(',',array_unique(explode(',',$suIds)));
                    $data[$key]['vim_ip']           = isset($value[1]) ? $value[1] : '';
                    $data[$key]['admin']            = isset($value[2]) ? $value[2] : '';
                    $data[$key]['vm_pass']          = isset($value[3]) ? $value[3] : '';
                    $data[$key]['subordinate_id']   = isset($suIds) ? $suIds : '';
                    unset($suIds);
                }

                if ($data) {
                    Db::name('nge_vminfo')->insertAll($data);
                    $this->success('导入成功' , url("manage/vminfoList"));
                }else{
                    $this->error('没有数据导入');
                }
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

    //虚拟机列表
	public function vminfoList()
	{
		$content = hook_one('admin_manage_agentlist_view');
		if (!empty($content)) {
			return $content;
		}

        $vim_ip = trim($this->request->param('vim_ip'));
        $where = [];
        if($vim_ip){
            $where['vim_ip'] = ['like','%'.$vim_ip.'%'];
        }

		$vminfos = Db::name('nge_vminfo')
					->order(['id DESC'])
                    ->where($where)
                    // ->limit(30)
					->select()
					->toArray();

		foreach ($vminfos as $vk => $vv) {
			$subordinate = Db::name('nge_subordinate')->where('id','in',$vv['subordinate_id'])->select()->toArray();
			foreach ($subordinate as $k => $v) {
				$subordinate[$k]['fanpages'] = Db::name('nge_fanpages')->where(['subordinate_id' => $v['id']])->select()->toArray();
			}
			
			$vminfos[$vk]['subordinate'] = $subordinate;

		}

        //分页
        $curpage = input('page') ? input('page') : 1;//当前第x页，有效值为：1,2,3,4,5...
        $listRow = 20;//每页行记录
        // $showdata = array_chunk($vminfos[$curpage-1], count($vminfos[0]), true);
        $showdata = array_slice($vminfos, ($curpage - 1)*$listRow, $listRow,true);
        
        $p = Bootstrap::make($showdata, $listRow, $curpage, count($vminfos), false, [
            'var_page' => 'page',
            'path'     => url('/admin/manage/vminfolist'),//这里根据需要修改url
            'query'    => [],
            'fragment' => '',
        ]);
        
        $p->appends($_GET);

		$this->assign('vminfos',$p);
        $this->assign('plistpage', $p->render());
		return $this->fetch();
	}


	// //nge_mainbody表添加
	// public function mainbodyExcelAdd()
	// {
	// 	if($this->request->isPost()){

	// 		$excel_name = $this->request->param('excel_name');
 //        	$excel_url = $this->request->param('excel_url');

 //        	if(!empty($excel_name) && !empty($excel_url)){

 //        		$excelData = $this->excelRead($excel_url);

 //                if ($excelData[0][0] != '公司名称') {
 //                    $this->error('确认表格是否正确');
 //                }

 //                array_shift($excelData);  //删除第一个数组(标题);

 //        		$mainbody = Db::name('nge_mainbody')->select()->toArray();

 //        		$data = [];
	
 //                $mainbodyAllCn = [];
 //                $mainbodyAllEn = [];
 //                //更新数据标识
 //                $isUpdate = false;

 //        		foreach ($mainbody as $mmk => $mmv) {
 //                    $mainbodyAllCn[$mmv['cn_name']] = $mmv['id'];
 //                    $mainbodyAllEn[$mmv['en_name']] = $mmv['id'];
 //        		}

 //        		foreach ($excelData as $key => $value) {
 //                    if (strlen($value[1]) <= 0 && strlen($value[0]) <= 0 ) {
 //                        break;
 //                    }
 //        			//匹配是否有重复信息,重复信息跳过
 //                    if (isset($mainbodyAllEn[$value[1]]) && isset($mainbodyAllCn[$value[0]])) {
 //                        continue;
 //                    }elseif (!isset($mainbodyAllEn[$value[1]]) && !isset($mainbodyAllCn[$value[0]])) {
 //                        $data[$key]['cn_name']  = isset($value[0]) ? $value[0] : '';
 //                        $data[$key]['en_name']  = isset($value[1]) ? $value[1] : '';
 //                    }else{
 //                        //表id
 //                        $mid = isset($mainbodyAllEn[$value[1]]) ? $mainbodyAllEn[$value[1]] : $mainbodyAllCn[$value[0]];

 //                        $upData = [
 //                            'id'      => $mid,
 //                            'cn_name' => $value[0],
 //                            'en_name' => $value[1],
 //                        ];

 //                        Db::name('nge_mainbody')->update($upData);
 //                        $isUpdate = true;
 //                    }

 //        		}
                                                                                                    
 //        		if ($data) {
 //        			Db::name('nge_mainbody')->insertAll($data);
 //        			$this->success('导入成功' , url("manage/mainbodyList"));
 //        		}elseif ($isUpdate == true) {
 //                    $this->success('更新成功' , url("manage/mainbodyList"));
 //                }else{
 //        			$this->error('没有数据导入');
 //        		}
        		
 //        	}else{
 //        		$this->error('请上传Excel文件');
 //        	}
	// 	}
	// }

        //nge_mainbody表添加
    public function mainbodyExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excelData = $this->excelRead($excel_url);

                if ($excelData[0][1] != '公司名称') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excelData);  //删除第一个数组(标题);z

                $mainNum = Db::name('nge_mainbody')->count('id');

                $data = [];

                foreach ($excelData as $key => $value) {
                    if (strlen($value[1]) <= 0 && strlen($value[2]) <= 0 ) {
                        break;
                    }

                    $data['cn_name']        = $value[1];
                    $data['en_name']        = $value[2];
                    $data['status']         = $value[3];
                    $data['isdown_status']  = $value[4];

	
                    //匹配是否有重复信息,重复信息跳过
                    if ($value[0] > $mainNum ) {
                        //新增
                        $re = Db::name('nge_mainbody')->insert($data);
                    }else{
                        $re = Db::name('nge_mainbody')->where('id',$value[0])->update($data);
                    }

                }
                                                                                                    
                if ($re) {
                    $this->success('导入成功' , url("manage/mainbodyList"));
                }else{
                    $this->error('导入失败');
                }
                
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

	//主体列表
	public function mainbodyList()
	{
		$content = hook_one('admin_manage_mainbodylist_view');
		if (!empty($content)) {
			return $content;
		}

        $name = trim($this->request->param('name'));
        $where = [];
        if($name){
            $where['cn_name|en_name'] = ['like','%'.$name.'%'];
        }

		$mainbodys = Db::name('nge_mainbody')
                        ->where($where)
                        ->order(['id DESC'])
                        ->paginate(50, false, [
                            'query' => request()->param(),
                        ]);
        $page = $mainbodys->render();
        $this->assign("page", $page);
		$this->assign('mainbodys',$mainbodys);

		return $this->fetch();
	}

    public function editmainbody(){
        $id = $this->request->param('id', 0, 'intval');
        $mainbody = DB::name('nge_mainbody')->where(["id" => $id])->find();
        $this->assign($mainbody);
        return $this->fetch();
    }

    function editPostMainbody(){
        if ($this->request->isPost()) {
            if (!empty($_POST['id'])) {
                $result = DB::name('nge_mainbody')->update($_POST);
                if(!$result){
                    $this->error("修改失败！");
                    return;
                }
                $this->success("修改成功！", url("manage/mainbodyList"));
            } else {
                $this->error("ID不能为空！");
            }
        }
    }

    //cmf_nge_record表添加
    public function recordExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excelData = $this->excelRead($excel_url);

                if ($excelData[0][0] != '部门负责人' && $excelData[0][1] != '主体') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excelData);  //删除第一个数组(标题);

                //需要信息:domain,maninbody,agent,fanpages,advertisingaccount,
                $domain = Db::name('nge_domain')->field('id,domain')->select()->toArray();
                $mainbody = Db::name('nge_mainbody')->field('id,cn_name')->select()->toArray();
                $agent = Db::name('nge_agent')->field('id,name')->select()->toArray();
                $fanpage = Db::name('nge_fanpages')->field('id,url')->select()->toArray();
                $advertisingaccount = Db::name('nge_advertisingaccount')->field('id,account_num')->select()->toArray(); 

                foreach ($domain as $dk => $dv) {
                    $domains[$dv['domain']] = $dv['id'];
                }

                foreach ($mainbody as $mk => $mv) {
                    $mainbodys[$mv['cn_name']] = $mv['id'];
                }

                foreach ($agent as $ak => $av) {
                    $agents[$av['name']] = $av['id'];
                }

                foreach ($fanpage as $fk => $fv) {
                    $fanpages[$fv['url']] = $fv['id'];
                }

                foreach ($advertisingaccount as $adk => $adv) {
                    $advertisingaccounts[$adv['account_num']] = $adv['id'];
                }

                $data = [];
                $flag = 0;

                //数据中一条对应多条数据的,整理成数组
                foreach ($excelData as $ek => $ev) {
                    if (strlen($ev[0]) <= 0 && strlen($ev[1]) <= 0 && strlen($ev[2]) <= 0 && strlen($ev[3]) <= 0 && strlen($ev[4]) <= 0 && strlen($ev[5]) <= 0 && strlen($ev[6]) <= 0 && strlen($ev[7]) <= 0 && strlen($ev[8]) <= 0 && strlen($ev[9]) <= 0 && strlen($ev[10]) <= 0 && strlen($ev[11]) <= 0) {
                        unset($excelData[$ek]);
                        continue;
                    }
                    $sDomain = strtolower($ev[2]);

                    if (strlen($ev[2]) > 1 && !isset($domains[$sDomain])) {
                        @$errData .= '域名  '.$ev[2]."   不存在 \r\n <br/>";
                    }
                    if (strlen($ev[1]) > 1 && !isset($mainbodys[$ev[1]])) {
                        @$errData .= '主体  '.$ev[1]."    不存在 \r\n <br/>";
                    }
                    if (strlen($ev[4]) > 1 && !isset($agents[$ev[4]])) {
                        @$errData .= '代理  '.$ev[4]."    不存在 \r\n <br/>";
                    }

                    if(($ev[0] == null && $ev[1] == null && $ev[2] == null && $ev[3] == null && $ev[4] == null) && ($ev[8] != null || $ev[11] != null)){
                        $flag +=1;
                        $a['fs'] = str_replace(chr(194) . chr(160), "", $ev[8]);
                        $a['ada'] = str_replace(chr(194) . chr(160), "", $ev[11]);
                        // $a['platform'] = $ev[4];

                        $excelData[$ek-$flag][100][] = $a;

                        unset($excelData[$ek]);
                    }else{
                        $flag = 0;
                        $a['fs'] = str_replace(chr(194) . chr(160), "", $ev[8]);
                        $a['ada'] = str_replace(chr(194) . chr(160), "", $ev[11]);
                        // $a['platform'] = $ev[4];

                        $excelData[$ek][100][] = $a;
                    }
                }

                //重置数组键序
                $excelData = array_merge($excelData);

                foreach ($excelData as $key => $value) {

                    $upNum = 0;

                    $sDomain = strtolower($value[2]);

                    $data[$key]['domain_id']        = isset($domains[$sDomain]) ? $domains[$sDomain] : @$errData .= '域名  '.$value[2]."   不存在,请导入后再操作 \r\n";
                    $data[$key]['main_id']          = isset($mainbodys[$value[1]]) ? $mainbodys[$value[1]] : @$errData .= '主体  '.$value[1]."    不存在,请导入后再操作 \r\n";
                    $data[$key]['agent_id']         = isset($agents[$value[4]]) ? $agents[$value[4]] : @$errData .= '代理  '.$value[4]."    不存在,请导入后再操作 \r\n";
                    $data[$key]['apply_time']       = isset($value[9]) ? strtotime($value[9]) : '';
                    $data[$key]['platform']         = $value[3];
                    $data[$key]['apply_num']        = $value[5];

                    if (@$errData) {
                        echo $key;
                        echo $errData;die;
                        // $this->error($errData);
                    }

                    foreach ($value[100] as $k => $v) {

                        if($v['fs'] != null && !isset($fanpages[$v['fs']])){
                           echo $v['fs']."    不存在,请导入后再操作 \r\n";die;
                        }

                        //获取对应粉丝页的ID
                        if ($v['fs'] != null) {
                            $fanpages_ids = isset($fanpages_ids) ? $fanpages_ids.','.$fanpages[$v['fs']] : $fanpages[$v['fs']]; 
                        }

                        //判断是否是有户下来,有户需要插入广告账户表nge_advertisingaccount
                        if ($v['ada'] != null && !isset($advertisingaccounts[$v['ada']])) {
                            $upNum = 1;
                            $adaData = [
                                'agent_id'      => $data[$key]['agent_id'],
                                'account_num'   => strval($v['ada']),
                                'status'        => 1,
                                'going_time'    => time(),
                            ];
                            Db::name('nge_advertisingaccount')->insert($adaData);
                            $adaId = Db::name('nge_advertisingaccount')->getLastInsID();
                            $adaIds = isset($adaIds) ? $adaIds.','.$adaId : $adaId;
                        }

                        //户表中如果有现在的数据,记录下来id
                        if (isset($advertisingaccounts[$v['ada']])) {
                            $adaIds = isset($adaIds) ? $adaIds.','.$advertisingaccounts[$v['ada']] : $advertisingaccounts[$v['ada']];
                        }
    
                    }

                    if (isset($errData)) {
                        $this->error($errData,'',10);die;
                    }

                    $data[$key]['fanpages_ids']     = $fanpages_ids;
                    $data[$key]['account_id']       = isset($adaIds) ? $adaIds : '';
                    unset($fanpages_ids);
                    unset($adaIds);

                    //判断是否有更新的数据
                    if (count($data[$key]['account_id']) > 0) {
                        $upNum = 0;
                        $recordId = Db::name('nge_record')->field('id,fanpages_ids')->where(['domain_id' => $domains[$sDomain],'fanpages_ids' => $data[$key]['fanpages_ids']])->find();

                        if ($recordId != null && ($recordId['fanpages_ids'] == $data[$key]['fanpages_ids'])) {
                            Db::name('nge_record')->where(['id' => $recordId['id'],'fanpages_ids' => $recordId['fanpages_ids']])->update($data[$key]);
                            $upNum ++;
                            unset($data[$key]);
                        }
                    }else{
                        $recordId = Db::name('nge_record')->where(['domain_id' => $domains[$sDomain],'fanpages_ids' => $data[$key]['fanpages_ids'],'main_id' => $mainbodys[$value[1]],'agent_id' => $agents[$value[4]]])->find();

                        if ($recordId !== null) {
                            unset($data[$key]);
                            continue;
                        }
                    }

                }
                //重置数组键序
                $data = array_merge($data);

                if ($data && $upNum == 0) {
                    Db::name('nge_record')->insertAll($data);
                    $this->success('导入成功' , url("manage/recordList"));
                }elseif($upNum >0 ){
                    $this->success('数据更新成功' , url("manage/recordList"));
                }else{
                    $this->error('没有数据导入');
                }
                
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

	//记录列表
	public function recordList()
	{
		$content = hook_one('admin_manage_recordlist_view');
		if (!empty($content)) {
			return $content;
		}

		$domain = trim($this->request->param('domain'));

		$where = [];
		$whereor = [];
        if($domain){
            $where['d.domain'] = ['like','%'.$domain.'%'];
            $whereor['m.cn_name'] = ['like','%'.$domain.'%'];
            $whereor['d.stationmaster'] = ['like','%'.$domain.'%'];
        }


		$records = Db::name('nge_record')
						->alias('r')
                        ->field('r.*,d.domain,d.group,d.stationmaster,m.cn_name,m.en_name')
						->join('nge_domain d','r.domain_id = d.id')
						->join('nge_mainbody m','m.id = r.main_id')
						->where($where)
						->whereOr($whereor)
						->order(['r.id DESC'])
						// ->limit(30)
						->select()
						->toArray();
			
		foreach ($records as $key => $value) {
			$records[$key]['fanpages'] = Db::name('nge_fanpages')->where('id','in',$value['fanpages_ids'])->select()->toArray();
			$records[$key]['agents'] = Db::name('nge_agent')->where('id','in',$value['agent_id'])->select()->toArray();
			$records[$key]['advertisingaccount'] = Db::name('nge_advertisingaccount')->where('id','in',$value['account_id'])->select()->toArray();
		}

        //分页
        $curpage = input('page') ? input('page') : 1;//当前第x页，有效值为：1,2,3,4,5...
        $listRow = 20;//每页行记录
        $showdata = array_slice($records, ($curpage - 1)*$listRow, $listRow,true);
        
        $p = Bootstrap::make($showdata, $listRow, $curpage, count($records), false, [
            'var_page' => 'page',
            'path'     => url('/admin/manage/recordlist'),//这里根据需要修改url
            'query'    => request()->param(),
            'fragment' => '',
        ]);
        
        $p->appends($_GET);

        $this->assign('records',$p);
        $this->assign('plistpage', $p->render());

		return $this->fetch();
	}


    //没有虚拟机粉丝页导入
    public function fanpagesExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excelData = $this->excelRead($excel_url);

                if ($excelData[0][1] != '域名' && $excelData[0][3] != '对应粉丝页') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excelData);  //删除第一个数组(标题);

                $fanpage = Db::name('nge_fanpages')->field('id,url')->select()->toArray();
                $subordinate = Db::name('nge_subordinate')->field('id,accountnumber')->select();
                $domain = Db::name('nge_domain')->field('id,domain')->select()->toArray();

                $fanpageAll = [];
                $subordinateAll = [];
                $domainAll = [];

                foreach ($fanpage as $fk => $fv) {
                    $fanpageAll[$fv['url']] = $fv['id'];
                }

                foreach ($subordinate as $sk => $sv) {
                    $subordinateAll[$sv['accountnumber']] = $sv['id'];
                }

                foreach ($domain as $dk => $dv) {
                    $domainAll[$dv['domain']] = $dv['id'];
                }

                $data = [];

                //验证一遍数据
                foreach ($excelData as $ck => $cv) {
                    if (strlen($cv[0]) <= 0 && strlen($cv[1]) <= 0 && strlen($cv[2]) <= 0 && strlen($cv[3]) <=0 ) {
                        break;
                    }
                    $oneDomain = strtolower($cv[1]);
                    if (!isset($domainAll[$oneDomain]) && strlen($cv[1]) > 0) {
                        @$errData .= $cv[1].'     没有此域名 <br/>';
                    }
                    // if (strlen($cv[3]) && isset($fanpageAll[$cv[3]])) {
                    //     @$errData .= $cv[3].'     粉丝页已存在 <br/>';
                    // }
                }
                if (@$errData) {
                    echo $errData;die;
                }

                foreach ($excelData as $key => $value) {
                    if (strlen($value[0]) <= 0 && strlen($value[1]) <= 0 && strlen($value[2]) <= 0 && strlen($value[3]) <= 0) {
                        break;
                    }
                    //统一大小写
                    $oneDomain = strtolower($value[1]);
                    //有此FB账号和粉丝页跳出
                    if (isset($subordinateAll[$value[0]]) && isset($fanpageAll[$value[3]])) {
                        continue;
                    }

                    if (!isset($domainAll[$oneDomain]) && strlen($value[1]) > 0) {
                        $this->error($value[1].'     没有此域名');
                    }
                    //列数据如果为空提示
                    if ($value[1] == null || $value[3] == null) {
                        $this->error($value[1].$value[3].'  此行数据有空,请修改');
                    }

                    //有此FB账号没有粉丝页,添加此粉丝页
                    if (isset($subordinateAll[$value[0]]) && !isset($fanpageAll[$value[3]])) {
                        $data[$key]['url']            = $value[3];
                        $data[$key]['status']         = 1;
                        $data[$key]['subordinate_id'] = $subordinateAll[$value[0]];
                        $data[$key]['in_bm']          = $value[2];
                        $data[$key]['create_time']    = time();
                        $data[$key]['domain_id']      = $domainAll[$oneDomain];
                        continue;
                    }

                    //没有此FB账号但已有此粉丝页,提示去查正粉丝页信息并去后台更改
                    if (!isset($subordinateAll[$value[0]]) && isset($fanpageAll[$value[3]])) {
                        echo $value[3].'  已属于其他FB账号';die;
                        // $this->error($value[3].'  已属于其他FB账号,请查正后去后台更改',url("manage/fanpagesList"));
                    }

                    //没有此FB账号没有粉丝页,添加此FB账号和粉丝页
                    if (strlen($value[0]) >= 1 && !isset($subordinateAll[$value[0]]) && !isset($fanpageAll[$value[3]])) {
                        $subData = [
                            'accountnumber' => $value[0],
                            'bm'            => $value[2],
                            'creat_time'    => time()
                        ];
                        Db::name('nge_subordinate')->insert($subData);
                        $subId = Db::name('nge_subordinate')->getLastInsID();

                        $data[$key]['url']            = $value[3];
                        $data[$key]['status']         = 1;
                        $data[$key]['subordinate_id'] = $subId;
                        $data[$key]['in_bm']          = $value[2];
                        $data[$key]['create_time']    = time();
                        $data[$key]['domain_id']      = $domainAll[$oneDomain];
                    }

                    //没有FB的粉丝页直接添加数据
                    if (strlen($value[0]) <= 0 && isset($domainAll[$oneDomain]) && strlen($value[3]) > 0) {
                        $data[$key]['url']            = $value[3];
                        $data[$key]['status']         = 1;
                        $data[$key]['subordinate_id'] = '';
                        $data[$key]['in_bm']          = $value[2];
                        $data[$key]['create_time']    = time();
                        $data[$key]['domain_id']      = $domainAll[$oneDomain];
                    }
                }

                if ($data) {
                    Db::name('nge_fanpages')->insertAll($data);
                    // echo Db::name('nge_fanpages')->getLastSql();
                    $this->success('导入成功' , url("manage/fanpagesList"));
                }else{
                    $this->error('没有数据导入');
                }
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }




//粉丝页列表
    public function fanpagesList()
    {
        $content = hook_one('admin_manage_fanpageslist_view');
        if (!empty($content)) {
            return $content;
        }

        $url = trim($this->request->param('url'));
        $where = [];
        $whereor = [];
        if($url){
            $where['f.url'] = ['like','%'.$url.'%'];
            $whereor['s.accountnumber'] = ['like','%'.$url.'%'];
            $whereor['d.domain'] = ['like','%'.$url.'%'];
            $whereor['d.stationmaster'] = ['like','%'.$url.'%'];
        }

        $fanpages = Db::name('nge_fanpages')
                    ->alias('f')
                    ->field('f.*,s.accountnumber,s.password,s.bm,s.creat_time,d.domain,d.group,d.stationmaster')
                    ->join('nge_subordinate s' , 's.id = f.subordinate_id','LEFT')
                    ->join('nge_domain d' , 'd.id = f.domain_id','LEFT')
                    ->where($where)
                    ->whereOr($whereor)
                    ->order('f.id DESC')
                    ->paginate(20, false, [
                            'query' => request()->param(),
                        ]);

        $page = $fanpages->render();

        $this->assign("page", $page);
        $this->assign('fanpages',$fanpages);

        return $this->fetch();
    }

    public function editfanpage(){
        $id = $this->request->param('id', 0, 'intval');

        $fanpages = Db::name('nge_fanpages')
                    ->alias('f')
                    ->field('f.*,s.accountnumber,s.password,s.bm,s.creat_time')
                    ->join('nge_subordinate s','s.id = f.subordinate_id','LEFT')
                    ->where(['f.id'=>$id])
                    ->find();

        $fb = Db::name('nge_subordinate')
                ->field('id,accountnumber')
                ->select();

        $this->assign('fanpages',$fanpages);
        $this->assign('fb',$fb);
        return $this->fetch();
    }

    public function editPostFanpage(){
        if ($this->request->isPost()) {
            if (!empty($_POST['id'])) {
                // echo "<pre>";
                // print_r($_POST);
                // die;
                $tp = $_POST;
                $tp['update_time'] = time();
                $result = DB::name('nge_fanpages')->update($tp);
                if(!$result){
                    $this->error("修改失败！");
                    return;
                }
                $this->success("修改成功！", url("manage/fanpagesList"));
            } else {
                $this->error("ID不能为空！");
            }
        }
    }

	//修改粉丝页状态
	public function fanpagesChangeStatus()
	{
		$id = trim($this->request->param('id'));
		$this->request->post();
		$status = trim($this->request->param('status'));
		if ($status == 1) {
			$changeStatus = ['status' => 0,'update_time' => time()];
		}
		if ($status == 0) {
			$changeStatus = ['status' => 1,'update_time' => time()];
		}
		if(Db::name('nge_fanpages')->where(['id' => $id])->update($changeStatus)){
			$this->success('更改成功');
		}else{
			$this->error('更改失败');
		}
	}

	//广告账户列表
	public function advertisingaccountList()
	{
		$content = hook_one('admin_manage_advertisingaccountlist_view');
		if (!empty($content)) {
			return $content;
		}

        $account_num = trim($this->request->param('account_num'));
        $where = [];
        if($account_num){
            $where['ad.account_num'] = ['like','%'.$account_num.'%'];
        }

        $advertisingaccounts = Db::name('nge_advertisingaccount')
        						->alias('ad')
        						->field('ad.*,a.name')
        						->join('nge_agent a','ad.agent_id = a.id')
                    			->where($where)
                    			->order('ad.id DESC')
                    			->paginate(20);
        $page = $advertisingaccounts->render();
        $this->assign("page", $page);
		$this->assign('advertisingaccounts',$advertisingaccounts);

		return $this->fetch();
	}

	//修改粉丝页状态
	public function advertisingaccountChangeStatus()
	{
		$id = trim($this->request->param('id'));
		$status = trim($this->request->param('status'));
		if ($status == 1) {
			$changeStatus = ['status' => 0];
		}
		if ($status == 0) {
			$changeStatus = ['status' => 1];
		}
		if(Db::name('nge_advertisingaccount')->where(['id' => $id])->update($changeStatus)){
			$this->success('更改成功');
		}else{
			$this->error('更改失败');
		}
	}

	/**
	 *读取excel数据
	 *string $excel_url  excel路径
	 *return array $excel_array 读取数组
	 */
	public function excelRead($excel_url)
	{
        Loader::import('PHPExcel.Classes.PHPExcel');
        Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5');
        $filename = ROOT_PATH.'public/upload/'.$excel_url;  //文件路径
        $extension = cmf_get_file_extension($excel_url);    //文件扩展名
        if($extension == 'xlsx'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
        }else if($extension == 'xls'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
        }else{
            $this->error('请上传Excel格式的文件！','');
        }

        $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式
        // array_shift($excel_array);  //删除第一个数组(标题);
        $excelArray = trimExcelStrBlank($excel_array);
        return TrimArray($excelArray);
        // return $excel_array;
	}

    // //代理据统计
    // public function agentTotal()
    // {
    //     $content = hook_one('admin_manage_agenttotal_view');
    //     if (!empty($content)) {
    //         return $content;
    //     }

    //     //查询时间段
    //     if (input('post.star_time') != '') {
    //         $star_time =  strtotime(input('post.star_time'));
    //         $end_time =  strtotime(input('post.end_time'));
    //         $where_time = " AND apply_time BETWEEN '$star_time' AND '$end_time' ";
    //     }else{
    //         $where_time = '';
    //     }

    //     $allAgent = Db::name('nge_agent')->order('id DESC')->select()->toArray();

    //     foreach ($allAgent as $key => $value) {
    //         //申户数
    //         $apply_num = Db::query("SELECT SUM(apply_num) total_apply_num FROM cmf_nge_record WHERE agent_id =".$value['id'] . $where_time);
    //         $allAgent[$key]['total_apply_num'] = isset($apply_num[0]['total_apply_num']) ? $apply_num[0]['total_apply_num'] : 0;

    //         //下户数
    //         $down_num = Db::query("SELECT COUNT(*) total_down_num FROM cmf_nge_advertisingaccount WHERE agent_id =".$value['id']);
    //         $allAgent[$key]['total_down_num'] = isset($down_num[0]['total_down_num']) ? $down_num[0]['total_down_num'] : 0;

    //         //提交站数
    //         $apply_domain_num = Db::query("SELECT COUNT(*) total_apply_domain_num FROM cmf_nge_record WHERE agent_id =".$value['id'] . $where_time);
    //         $allAgent[$key]['total_apply_domain_num'] = isset($apply_domain_num[0]['total_apply_domain_num']) ? $apply_domain_num[0]['total_apply_domain_num'] : 0;

    //         //下户站数
    //         $down_domain_num = Db::query("SELECT COUNT(*) total_down_domain_num FROM cmf_nge_record WHERE agent_id =".$value['id']." AND account_id != '' " . $where_time);
    //         $allAgent[$key]['total_down_domain_num'] = isset($down_domain_num[0]['total_down_domain_num']) ? $down_domain_num[0]['total_down_domain_num'] : 0;
    //     }

    //     $this->assign('allAgent',$allAgent);
    //     return $this->fetch();
    // }

    //代理据统计
    public function agentTotal()
    {
        $content = hook_one('admin_manage_agenttotal_view');
        if (!empty($content)) {
            return $content;
        }

        //查询时间段
        if (input('post.star_time') != '') {
            $star_time =  strtotime(input('post.star_time'));
            $end_time =  strtotime(input('post.end_time'));
            // $where_time = " AND apply_time BETWEEN '$star_time' AND '$end_time' ";
            $where_time['apply_time'] = array('between',array($star_time,$end_time));

        }else{
            $where_time = '';
        }

        $allAgent = Db::name('nge_agent')->order('id DESC')->select()->toArray();

        foreach ($allAgent as $key => $value) {
            //申户数
            $apply_num = Db::name('nge_record')->where(['agent_id'=>$value['id']])->where($where_time)->sum('apply_num');

            if ($apply_num > 0) {
                $all_agent[$key]['name'] = $value['name'];
                $all_agent[$key]['total_apply_num'] = $apply_num;

                //下户数
                $down_num = Db::name('nge_advertisingaccount')->where(['agent_id'=>$value['id']])->count('id');
                $all_agent[$key]['total_down_num'] = $down_num;

                //提交站数
                $apply_domain_num = Db::name('nge_record')->where(['agent_id'=>$value['id']])->where($where_time)->count('id');
                $all_agent[$key]['total_apply_domain_num'] = $apply_domain_num;

                //下户站数
                $down_domain_num = Db::name('nge_record')->where(['agent_id'=>$value['id']])->where($where_time)->where('account_id','neq','')->count('id');
                $all_agent[$key]['total_down_domain_num'] = $down_domain_num;
            }else{
                $all_agent[$key]['name'] = $value['name'];
                $all_agent[$key]['total_apply_num'] = 0;
                $all_agent[$key]['total_down_num'] = 0;
                $all_agent[$key]['total_apply_domain_num'] = 0;
                $all_agent[$key]['total_down_domain_num'] = 0;
            }

        }

        $all_agent = array_merge($all_agent);

        $this->assign('allAgent',$all_agent);
        return $this->fetch();

    }
    //phpexcel 代理统计导出
	public function agentTotalOut()
	{
        Loader::import("PHPExcel.PHPExcel");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel5");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel2007");
        Loader::import("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel    = new \PHPExcel();
        $objWriter      = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter      = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        //查询时间段
        if (input('post.star_time') != '') {
            $star_time =  strtotime(input('post.star_time'));
            $end_time =  strtotime(input('post.end_time'));
            // $where_time = " AND apply_time BETWEEN '$star_time' AND '$end_time' ";
            $where_time['apply_time'] = array('between',array($star_time,$end_time));

        }else{
            $where_time = '';
        }

        $allAgent = Db::name('nge_agent')->order('id DESC')->select()->toArray();

        foreach ($allAgent as $key => $value) {
            //申户数
            $apply_num = Db::name('nge_record')->where(['agent_id'=>$value['id']])->where($where_time)->sum('apply_num');

            if ($apply_num > 0) {
                $all_agent[$key]['name'] = $value['name'];
                $all_agent[$key]['total_apply_num'] = $apply_num;

                //下户数
                $down_num = Db::name('nge_advertisingaccount')->where(['agent_id'=>$value['id']])->count('id');
                $all_agent[$key]['total_down_num'] = $down_num;

                //提交站数
                $apply_domain_num = Db::name('nge_record')->where(['agent_id'=>$value['id']])->where($where_time)->count('id');
                $all_agent[$key]['total_apply_domain_num'] = $apply_domain_num;

                //下户站数
                $down_domain_num = Db::name('nge_record')->where(['agent_id'=>$value['id']])->where($where_time)->where('account_id','neq','')->count('id');
                $all_agent[$key]['total_down_domain_num'] = $down_domain_num;
            }else{
                $all_agent[$key]['name'] = $value['name'];
                $all_agent[$key]['total_apply_num'] = 0;
                $all_agent[$key]['total_down_num'] = 0;
                $all_agent[$key]['total_apply_domain_num'] = 0;
                $all_agent[$key]['total_down_domain_num'] = 0;
            }

        }

        $all_agent = array_merge($all_agent);

        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '代理')
        ->setCellValue('B1', '提交站数')
        ->setCellValue('C1', '申户数')
        ->setCellValue('D1', '下户站数')
        ->setCellValue('E1', '下户数');

        //$i=2; //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($all_agent); //计算有多少条数据

        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $all_agent[$i-2]['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $all_agent[$i-2]['total_apply_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $all_agent[$i-2]['total_apply_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $all_agent[$i-2]['total_down_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $all_agent[$i-2]['total_down_num']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->settitle(date("Y-m-d")); //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="代理申户下户数量统计表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
	}


    // //站长据统计
    // public function masterTotal()
    // {
    //     $content = hook_one('admin_manage_mastertotal_view');
    //     if (!empty($content)) {
    //         return $content;
    //     }
    //     //查询时间段
    //     if (input('post.star_time') != '') {
    //         $star_time =  strtotime(input('post.star_time'));
    //         $end_time =  strtotime(input('post.end_time'));
    //         $where_time['r.apply_time'] = array('between',array($star_time,$end_time));
    //     }else{
    //         $where_time = '';
    //     }

    //     //组及组长：一组=>张三
    //     $allStationmaster = Db::name('nge_domain')->distinct(true)->field('group,stationmaster')->select()->toArray();
    //     foreach ($allStationmaster as $ak => $av) {
    //         $all_stationmaster[$av['group']]['stationmaster'] = $av['stationmaster'];
    //         $all_stationmaster[$av['group']]['total_apply_domain_num'] = 0;
    //         $all_stationmaster[$av['group']]['total_down_domain_num'] = 0;
    //         $all_stationmaster[$av['group']]['total_apply_num'] = 0;
    //         $all_stationmaster[$av['group']]['total_down_num'] = 0;
    //     }

    //     //所有申户记录
    //     $records = Db::name('nge_record')
    //                     ->alias('r')
    //                     ->field('r.account_id,r.apply_num,r.apply_time,r.agent_id,d.domain,d.group,d.stationmaster,d.id  as domain_id')
    //                     ->join('nge_domain d','r.domain_id = d.id')
    //                     ->where($where_time)
    //                     ->order(['r.id DESC'])
    //                     ->select()
    //                     ->toArray();
    //     if (!$records) {
    //         $this->error('没有数据');
    //     }
    //     foreach ($records as $key => $value) {
    //         //统计总数
    //         $total_num['total_apply_domain_num'] = $key + 1;
    //         $total_num['total_apply_num'] = isset($total_num['total_apply_num']) ? $total_num['total_apply_num'] + $value['apply_num'] : $value['apply_num'];
    //         if ($value['account_id'] != '') {
    //             $total_num['total_down_domain_num'] = isset($total_num['total_down_domain_num']) ? $total_num['total_down_domain_num'] + 1 : 1;
    //             $commaCount = substr_count($value['account_id'],',');
    //             $total_num['total_down_num'] = isset($total_num['total_down_num']) ? $total_num['total_down_num'] + $commaCount + 1 : $commaCount + 1;
    //         }
            
    //         //分组统计
    //         foreach ($all_stationmaster as $group => $stationmaster) {
    //             if ($value['group'] == $group) {
    //                 $all_stationmaster[$group]['total_apply_domain_num'] ++;
    //                 $all_stationmaster[$group]['total_apply_num'] = $all_stationmaster[$group]['total_apply_num'] + $value['apply_num'];
    //                 if ($value['account_id'] != '') {
    //                     $all_stationmaster[$group]['total_down_domain_num'] ++;
    //                     $comma_count = substr_count($value['account_id'],',');
    //                     $all_stationmaster[$group]['total_down_num'] =  $all_stationmaster[$group]['total_down_num'] + $comma_count + 1;
    //                 }
    //             }
    //         }

    //     }

    //     foreach ($all_stationmaster as $allk => $allv) {
    //         if ($allv['total_apply_domain_num'] == 0) {
    //             $all_stationmaster[$allk]['success_rate'] = '0';
    //         }else{
    //             $all_stationmaster[$allk]['success_rate'] = round($allv['total_down_domain_num'] / $allv['total_apply_domain_num'],2) * 100 .'%';
    //         }
            
    //     }

    //     $total_num['success_rate'] = round($total_num['total_down_domain_num'] / $total_num['total_apply_domain_num'],2) * 100 .'%'; 
    //     $total_num['stationmaster'] = '';

    //     $all_stationmaster['总计'] = $total_num;

    //     $this->assign('all_stationmaster',$all_stationmaster);
    //     return $this->fetch();
    // }

    //站长据统计
    public function masterTotal(){
        $content = hook_one('admin_manage_mastertotal_view');
        if (!empty($content)) {
            return $content;
        }

        //组别
        $nge_group = [
            '独立站三部' => ['son_group'=>'独立站五部'],
            '独立站九部' => ['son_group'=>'独立站六部,独立站十四部,独立站十八部,独立站二十二部'],
            '独立站十一部' => ['son_group'=>'独立站十部,独立站二十一部'],
            '独立站十三部' => ['son_group'=>'独立站十九部,独立站二十部'],
            '独立站十五部' => ['son_group'=>'独立站二十三部'],
            '独立站十六部' => ['son_group'=>'独立站十二部,独立站十七部'],
            // '独立站七部' => ['son_group'=> ''],
            '独立站八部' => ['son_group'=> ''],
        ];

        //查询时间段
        if (input('post.star_time') != '') {
            $star_time =  strtotime(input('post.star_time'));
            $end_time =  strtotime(input('post.end_time'));
            // $where_time = ['r.apply_time','between',[$star_time,$end_time]];
            $where_time['r.apply_time'] = array('between',array($star_time,$end_time));
        }else{
            $where_time = '';
        }

        //组及组长：一组=>张三
        $allStationmaster = Db::name('nge_domain')->distinct(true)->field('group,stationmaster')->select()->toArray();

        foreach ($allStationmaster as $ak => $av) {
            foreach ($nge_group as $key => $value) {
                if ($key == $av['group']) {
                    $nge_group[$key]['stationmaster'] = $av['stationmaster'];
                    $nge_group[$key]['total_apply_domain_num'] = 0;
                    $nge_group[$key]['total_down_domain_num'] = 0;
                    $nge_group[$key]['total_apply_num'] = 0;
                    $nge_group[$key]['total_down_num'] = 0;
                }
            }
        }

        //所有申户记录
        $records = Db::name('nge_record')
                        ->alias('r')
                        ->field('r.account_id,r.apply_num,r.apply_time,r.agent_id,d.domain,d.group,d.stationmaster,d.id  as domain_id')
                        ->join('nge_domain d','r.domain_id = d.id')
                        ->where($where_time)
                        ->order(['r.id DESC'])
                        ->select()
                        ->toArray();

        if (!$records) {
            $this->error('没有数据');
        }

        foreach ($records as $key => $value) {
            //去掉刘宇强组数据
            if ($value['stationmaster'] == '刘宇强') {
                continue;
            }
            //统计总数
            $total_num['total_apply_domain_num'] = $key + 1;
            $total_num['total_apply_num'] = isset($total_num['total_apply_num']) ? $total_num['total_apply_num'] + $value['apply_num'] : $value['apply_num'];
            if ($value['account_id'] != '') {
                $total_num['total_down_domain_num'] = isset($total_num['total_down_domain_num']) ? $total_num['total_down_domain_num'] + 1 : 1;
                $commaCount = substr_count($value['account_id'],',');
                $total_num['total_down_num'] = isset($total_num['total_down_num']) ? $total_num['total_down_num'] + $commaCount + 1 : $commaCount + 1;
            }
            
            //分组统计
            foreach ($nge_group as $group => $stationmaster) {
                if ($value['group'] == $group || strpos($stationmaster['son_group'],$value['group'])) {
                    $nge_group[$group]['total_apply_domain_num'] ++;
                    $nge_group[$group]['total_apply_num'] = $nge_group[$group]['total_apply_num'] + $value['apply_num'];
                    if ($value['account_id'] != '') {
                        $nge_group[$group]['total_down_domain_num'] ++;
                        $comma_count = substr_count($value['account_id'],',');
                        $nge_group[$group]['total_down_num'] =  $nge_group[$group]['total_down_num'] + $comma_count + 1;
                    }
                }
            }
        }

        //计算成功率
        foreach ($nge_group as $allk => $allv) {
            $nge_group[$allk]['group'] = $allk;
            if ($allv['total_apply_domain_num'] == 0) {
                $nge_group[$allk]['success_rate'] = '0';
            }else{
                $nge_group[$allk]['success_rate'] = round($allv['total_down_domain_num'] / $allv['total_apply_domain_num'],2) * 100 .'%';
            }   
        }


        $total_num['success_rate'] = round($total_num['total_down_domain_num'] / $total_num['total_apply_domain_num'],2) * 100 .'%'; 
        $total_num['stationmaster'] = '';
        $total_num['group'] = '总计';

        $nge_group['总计'] = $total_num;

        foreach ($nge_group as $k => $v) {
            $outAll[] = $v;
        }

        $this->assign('outAll',$outAll);
        return $this->fetch();

    }

    //站长据统计导出
    public function masterTotalOut()
    {
        //组别
        $nge_group = [
            '独立站三部' => ['son_group'=>'独立站五部'],
            '独立站九部' => ['son_group'=>'独立站六部,独立站十四部,独立站十八部,独立站二十二部'],
            '独立站十一部' => ['son_group'=>'独立站十部,独立站二十一部'],
            '独立站十三部' => ['son_group'=>'独立站十九部,独立站二十部'],
            '独立站十五部' => ['son_group'=>'独立站二十三部'],
            '独立站十六部' => ['son_group'=>'独立站十二部,独立站十七部'],
            // '独立站七部' => ['son_group'=> ''],
            '独立站八部' => ['son_group'=> ''],
        ];
        Loader::import("PHPExcel.PHPExcel");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel5");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel2007");
        Loader::import("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel    = new \PHPExcel();
        $objWriter      = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter      = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        //查询时间段
        if (input('post.star_time') != '') {
            $star_time =  strtotime(input('post.star_time'));
            $end_time =  strtotime(input('post.end_time'));
            // $where_time = ['r.apply_time','between',[$star_time,$end_time]];
            $where_time['r.apply_time'] = array('between',array($star_time,$end_time));
        }else{
            $where_time = '';
        }

        
        //组及组长：一组=>张三
        $allStationmaster = Db::name('nge_domain')->distinct(true)->field('group,stationmaster')->select()->toArray();
        foreach ($allStationmaster as $ak => $av) {
            foreach ($nge_group as $key => $value) {
                if ($key == $av['group']) {
                    $nge_group[$key]['stationmaster'] = $av['stationmaster'];
                    $nge_group[$key]['total_apply_domain_num'] = 0;
                    $nge_group[$key]['total_down_domain_num'] = 0;
                    $nge_group[$key]['total_apply_num'] = 0;
                    $nge_group[$key]['total_down_num'] = 0;
                }
            }
            // $all_stationmaster[$av['group']]['stationmaster'] = $av['stationmaster'];
            // $all_stationmaster[$av['group']]['total_apply_domain_num'] = 0;
            // $all_stationmaster[$av['group']]['total_down_domain_num'] = 0;
            // $all_stationmaster[$av['group']]['total_apply_num'] = 0;
            // $all_stationmaster[$av['group']]['total_down_num'] = 0;
        }

        //所有申户记录
        $records = Db::name('nge_record')
                        ->alias('r')
                        ->field('r.account_id,r.apply_num,r.apply_time,r.agent_id,d.domain,d.group,d.stationmaster,d.id  as domain_id')
                        ->join('nge_domain d','r.domain_id = d.id')
                        ->where($where_time)
                        ->order(['r.id DESC'])
                        ->select()
                        ->toArray();

        if (!$records) {
            $this->error('没有数据');
        }

        foreach ($records as $key => $value) {
            //去掉刘宇强组数据
            if ($value['stationmaster'] == '刘宇强') {
                continue;
            }
            //统计总数
            $total_num['total_apply_domain_num'] = $key + 1;
            $total_num['total_apply_num'] = isset($total_num['total_apply_num']) ? $total_num['total_apply_num'] + $value['apply_num'] : $value['apply_num'];
            if ($value['account_id'] != '') {
                $total_num['total_down_domain_num'] = isset($total_num['total_down_domain_num']) ? $total_num['total_down_domain_num'] + 1 : 1;
                $commaCount = substr_count($value['account_id'],',');
                $total_num['total_down_num'] = isset($total_num['total_down_num']) ? $total_num['total_down_num'] + $commaCount + 1 : $commaCount + 1;
            }
            
            //分组统计
            foreach ($nge_group as $group => $stationmaster) {
                if ($value['group'] == $group || strpos($stationmaster['son_group'],$value['group'])) {
                    $nge_group[$group]['total_apply_domain_num'] ++;
                    $nge_group[$group]['total_apply_num'] = $nge_group[$group]['total_apply_num'] + $value['apply_num'];
                    if ($value['account_id'] != '') {
                        $nge_group[$group]['total_down_domain_num'] ++;
                        $comma_count = substr_count($value['account_id'],',');
                        $nge_group[$group]['total_down_num'] =  $nge_group[$group]['total_down_num'] + $comma_count + 1;
                    }
                }
            }
        }

        //计算成功率
        foreach ($nge_group as $allk => $allv) {
            $nge_group[$allk]['group'] = $allk;
            if ($allv['total_apply_domain_num'] == 0) {
                $nge_group[$allk]['success_rate'] = '0';
            }else{
                $nge_group[$allk]['success_rate'] = round($allv['total_down_domain_num'] / $allv['total_apply_domain_num'],2) * 100 .'%';
            }   
        }


        $total_num['success_rate'] = round($total_num['total_down_domain_num'] / $total_num['total_apply_domain_num'],2) * 100 .'%'; 
        $total_num['stationmaster'] = '';
        $total_num['group'] = '总计';

        $nge_group['总计'] = $total_num;

        foreach ($nge_group as $k => $v) {
            $outAll[] = $v;
        }

        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '部门')
        ->setCellValue('B1', '站长')
        ->setCellValue('C1', '提交站数')
        ->setCellValue('D1', '申户数')
        ->setCellValue('E1', '下户站数')
        ->setCellValue('F1', '下户数')
        ->setCellValue('G1', '成功率');
        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        //$i=2; //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($outAll); //计算有多少条数据
        // echo $count;
        // die;
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $outAll[$i-2]['group']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $outAll[$i-2]['stationmaster']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $outAll[$i-2]['total_apply_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $outAll[$i-2]['total_apply_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $outAll[$i-2]['total_down_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $outAll[$i-2]['total_down_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $outAll[$i-2]['success_rate']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->settitle(date("Y-m-d")); //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="站长申户下户数量统计表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件

    }

	public function index()
	{
		$content = hook_one('admin_domainname_index_view');
		if (!empty($content)) {
			return $content;
		}
		return $this->fetch();
	}
}
